const mysql = require('mysql');
const {
	getMSTime,
} = require('./dateUtils')

const _FUNCS = ['ABS', 'ABS', 'BIN', 'CEILING', 'EXP', 'FLOOR', 'GREATEST', 'LEAST', 'LN', 'LOG', 'MOD', 'PI', 'RAND', 'ROUND', 'SIGN', 'SQRT', 'TRUNCATE', 'AVG', 'COUNT', 'MIN', 'MAX', 'SUM', 'GROUP_CONCAT', 'ASCII', 'BIT_LENGTH', 'CONCAT', 'CONCAT_WS', 'INSERT', 'FIND_IN_SET', 'LCASE', 'LOWER', 'LEFT', 'LENGTH', 'LTRIM', 'POSITION', 'QUOTE', 'REPEAT', 'REVERSE', 'RIGHT', 'RTRIM', 'STRCMP', 'TRIM', 'UCASE', 'UPPER', 'CURDATE', 'CURRENT_DATE', 'CURTIME', 'CURRENT_TIME', 'DATE_ADD', 'DATE_FORMAT', 'DATE_SUB', 'DAYOFWEEK', 'DAYOFMONTH', 'DAYOFYEAR', 'DAYNAME', 'FROM_UNIXTIME', 'HOUR', 'MINUTE', 'MONTH', 'MONTHNAME', 'NOW', 'QUARTER', 'WEEK', 'YEAR', 'AES_ENCRYPT', 'AES_DECRYPT', 'DECODE', 'ENCRYPT', 'ENCODE', 'MD5', 'PASSWORD', 'SHA', 'DATABASE', 'BENCHMARK', 'CONNECTION_ID', 'FOUND_ROWS', 'USER', 'SYSTEM_USER', 'VERSION']
const _funcSet = new Set(_FUNCS)

module.exports = class MySQL {
	constructor(options) {
		this.__options = options
		this.intervalId = ''
		this.__init()
		this.connectSum = 0
		this.poolConnectCount = 0
		this.poolConnectMax = 20
	}

	/**
	 * 获取mysql连接，断开后自动重新获取
	 * @return {[type]} [description]
	 */
	__init() {
		this.connectSum += 1
		if (this.intervalId)
			clearInterval(this.intervalId)
		this.intervalId = ''
		this.__options.connectionLimit = this.__options.connectionLimit || 20
		this.__pool = mysql.createPool(this.__options);
		this.__pool.getConnection((err, connection) => {
			this.intervalId = setInterval(() => {
				if (!connection || !connection.query) {
					clearInterval(this.intervalId)
					this.intervalId = ''
					return
				}
				connection.query('SELECT 1')
			}, 10000)
		})
	}

	__rebuildPool() {
		if (this.__pool) {
			this.__pool.end(err => {
				console.log('---MYSQL POOL END ERROR---', err)
				if (this.connectSum > 10)
					process.exit(1)
			})
		}
		this.__pool = null
		this.__init()
	}

	static strip(...args) {
		return parseValue.call(this, args)
	}

	static toUnicode(...args) {
		return toUnicode.call(this, args)
	}

	strip(...args) {
		return parseValue.call(this, args)
	}

	toUnicode(...args) {
		return toUnicode.call(this, args)
	}

	selectOne(params) {
		return parseOptions.call(this, params, 'query', 0)
	}

	select(params) {
		return parseOptions.call(this, params, 'query', -1)
	}

	insert(params) {
		return parseOptions.call(this, params, 'add', -1)
	}

	insertBatch(params) {
		return parseOptions.call(this, params, 'batch', -1)
	}

	update(params) {
		return parseOptions.call(this, params, 'update', -1)
	}

	remove(params) {
		return parseOptions.call(this, params, 'delete', -1)
	}

	executeSQL(SQL) {
		return doAction.call(this, SQL, -1)
	}

}

function parseOptions(options, type, idx) {
	if (!options)
		return Promise.reject('SQL错误！')

	if (typeof options == 'string')
		return doAction.call(this, options, idx)

	let {
		table,
		fields = [],
		updates = [],
		updateFeilds = [],
		updateValues = [],
		values = [],
		where = [],
		batch = [],
		condition = '',
	} = options
	fields = fields.map((val, idx) => {
		return val.replace(/[`'"\\\/]/g, '')
	})
	fields = fields.join(',')
	if (updateFeilds.length > 0 && updateValues.length == updateFeilds.length) {
		updates = updateFeilds.map((field, idx) => {
			let val = updateValues[idx]
			val = parseValue(val)
			return `${field} = ${val}`
		})
	}
	updates = updates.join(',')
	values = values.map(parseValue)
	values = values.join(',')
	batch = batch.join(',')
	where.unshift('1 = 1')
	where = where.join(' AND ')
	let SQL = ''
	if (type == 'query')
		SQL = `SELECT ${fields} FROM ${table} WHERE ${where} ${condition};`
	else if (type == 'add')
		SQL = `INSERT INTO ${table} (${fields}) VALUES (${values});`
	else if (type == 'update')
		SQL = `UPDATE ${table} SET ${updates} WHERE ${where};`;
	else if (type == 'batch')
		SQL = `INSERT INTO ${table} (${fields}) VALUES ${batch};`
	else if (type == 'delete')
		SQL = `DELETE FROM ${table} WHERE ${where};`;
	return doAction.call(this, SQL, idx)
}

function doAction(SQL, idx) {
	while (!this.__pool)
		this.__rebuildPool()
	return new Promise((resolve, reject) => {
		this.__pool.getConnection((err, connection) => {
			if (err) {
				console.log('---__pool.getConnection ERROR---', JSON.stringify(err))
				if (this.poolConnectCount > this.poolConnectMax) {
					return reject({
						type: '---__pool.getConnection ERROR---',
						err,
					})
				}
				this.poolConnectCount += 1
				return setTimeout(() => {
					this.__rebuildPool()
					doAction.call(this, SQL, idx)
				}, 2000)
			}
			connection.on('error', (err) => {
				console.log('---connection ON ERROR----', JSON.stringify(err))
				return reject({
					type: '---connection ON ERROR----',
					err,
				})
			})
			connection.query(SQL, (error, results, fields) => {
				if (error) {
					console.log('[---connection.query ERROR---]', `[${error.code}]`, `[${SQL}]`, `[${JSON.stringify(error)}]`)
					return reject({
						type: '---connection.query ERROR---',
						failed: true,
						err: error,
						sql: SQL,
					})
				}
				let rstr = JSON.stringify(results)
				if (typeof idx == 'number' && idx >= 0) {
					results = results && results[idx] || {}
				}
				return resolve(results);
			});
			connection.release();
		})
	})
}

function parseValue(val) {
	let options = null
	if (val && typeof val.val != 'undefined') {
		options = val
		val = options.val
	}
	let ignore = options && options.ignore || false
	let separator = options && options.separator
	let suffix = options && options.suffix || ''
	let prefix = options && options.prefix || ''
	if (val == 'NOW()')
		return val
	if (/^[A-Z]+\([\s\S]+\)$/.test(val) && _funcSet.has(val.replace(/\([\s\S]*\)$/, '')))
		return val
	if (typeof val == 'boolean' || typeof val == 'number')
		return val
	if (!val)
		val = ''
	val = val.trim()
	if (typeof val == 'string' && !ignore)
		val = val.replace(/\\/g, '\\\\')
	if (typeof val == 'string' && val.indexOf('"') < 0)
		return `"${prefix}${val}${suffix}"`
	if (typeof val == 'string' && val.indexOf("'") < 0)
		return `'${prefix}${val}${suffix}'`
	return `'${prefix}${val && val.replace(/'/g, "\\'") || ""}${suffix}'`
}

function toUnicode(str) {
	str = typeof str == 'string' ? str : ''
	let unicode = ''
	for (let i = 0; i < str.length; i++) {
		let uc = str[i].charCodeAt().toString(16)
		let z = ''
		if (uc.length < 4) {
			for (let j = 0; j < 4 - uc.length; j++)
				z += '0'
			uc = z + uc
		}
		unicode += `\\\\\\u${uc}`
	}
	return unicode
}